﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Security.Permissions;

namespace DataAccessLayer {
    public class DataProvider {
        private String _connectString = "Server=SAILORMOON-PC;Database=csdlnc;Trusted_Connection=True;";

        public String ConnectString {
            get { return _connectString; }
            set { _connectString = value; }
        }

        protected SqlCommand _sqlCmd = null;
        protected SqlDataAdapter _sqlAdapter = null;
        protected SqlConnection _sqlConn = null;

        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        public DataProvider() {
            _sqlConn = new SqlConnection(_connectString);
            _sqlConn = new SqlConnection(_connectString);
            _sqlCmd = new SqlCommand();
        }

        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        public DataProvider(String connectString) {
            _sqlConn = new SqlConnection(connectString);
        }

        /// <summary>
        /// execute select query to datatable
        /// </summary>
        /// <param name="query">select query</param>
        /// <returns>datatable by query</returns>
        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        protected DataTable executeQueryDataTable(String query) {
            try {
                DataTable table = new DataTable("table");
                _sqlAdapter = new SqlDataAdapter(query, _sqlConn);
                _sqlAdapter.Fill(table);

                return table;
            }
            catch (Exception ex) { return null; }
            
        }

        /// <summary>
        /// executes select query to dataset
        /// </summary>
        /// <param name="query">select query</param>
        /// <returns>dataset by query</returns>
        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        protected DataSet executeQueryDataSet(String query) {
            try {
                DataSet ds = new DataSet("dataset");
                _sqlAdapter = new SqlDataAdapter(query, _sqlConn);
                _sqlAdapter.Fill(ds);
                return ds;
            }
            catch (Exception ex) { return null; }
        }

        /// <summary>
        /// executes update/delete query 
        /// </summary>
        /// <param name="query"></param>
        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        protected void executeNonQuery(String query) {
            connect();

            _sqlCmd = new SqlCommand(query, _sqlConn);            
            _sqlCmd.ExecuteNonQuery();

            disConnect();
        }

        /// <summary>
        /// executes update/delete query and returns the number of rows affect 
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        protected int executeQuery(String query) {
            int result = 0;
            connect();

            _sqlCmd = new SqlCommand(query, _sqlConn);            
            result = _sqlCmd.ExecuteNonQuery();       
     
            disConnect();
            return result;
        }

        protected object executeScalar(String query) {
            object obj = null;
            connect();
            _sqlCmd = new SqlCommand(query, _sqlConn);            
            obj = _sqlCmd.ExecuteScalar();
            disConnect();
            return obj;
        }

        /// <summary>
        /// connect to datasource
        /// </summary>
        /// <returns></returns>
        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        protected bool connect() {
            try {
                _sqlConn.Open();
                return true;
            }
            catch (Exception ex) { return false; }
        }

        /// <summary>
        /// disconnect from datasource
        /// </summary>
        /// <returns></returns>
        [SecurityPermission(SecurityAction.Demand, ControlDomainPolicy = true)]
        protected bool disConnect() {
            try {
                _sqlConn.Close();
                return true;
            }
            catch (Exception ex) { return false; }
        }
    }
}
